package server.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import server.control.Settings;

public class CreateTable {

	private static Logger logger = Logger
			.getLogger(CreateTable.class.getName());

	public static int getVersion() {
		Connection con = ConnectionPool.getInstance().getConnection();
		PreparedStatement stmt = null;
		ResultSet res = null;
		String query = null;

		query = "SELECT dbVersion FROM DBMETA;";

		try {
			stmt = con.prepareStatement(query);
			res = stmt.executeQuery();
			int dbVersion = 0;
			if (res.next()) {
				dbVersion = res.getInt("dbVersion");
			} else {
				dbVersion = 0;
			}
			return dbVersion;
		} catch (SQLException e) {
			logger.debug(e);
			return 0;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				logger.warn("Couldn't close SQLInteraction. Exception: " + e);
			}
		}
	}

	private static boolean setDBVersion(int dbVersion) {
		Connection con = ConnectionPool.getInstance().getConnection();
		PreparedStatement stmt = null;
		String insert;

		
		if(dbVersion == 1){
			insert = "INSERT INTO `DBMETA` (`dbVersion`) " + "VALUES ('"
					+ dbVersion + "');";
		}else{
			insert = "UPDATE DBMETA SET dbVersion = " + dbVersion + " WHERE metaid = 1";
		}
		

		try {
			stmt = con.prepareStatement(insert);
			stmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			logger.warn(e);
			return false;
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				logger.warn("Couldn't close SQLInteraction. Exception: " + e);
			}
		}
	}

	/**
	 * Updatet die Datenbank Tabellen auf die currentVersion (siehe
	 * Settings.class) returns the new DBVersion
	 * 
	 * @param dbVersion
	 * @return the DB Version that it has been updated to
	 */
	public static int updateTables(int dbVersion) throws Exception{

		Connection con = ConnectionPool.getInstance().getConnection();
		PreparedStatement stmt = null;

		if (dbVersion < 1) {
			String createUserTable = null;
			String createDBMetaTable = null;
			createUserTable = "CREATE TABLE USER( "
					+ " uid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT "
					+ " , surname VARCHAR(32) " + " , firstname VARCHAR(32) "
					+ " , email VARCHAR(50) NOT NULL "
					+ " , password VARCHAR(256) NOT NULL " + " , points INT "
					+ " , PRIMARY KEY (uid)); ";

			createDBMetaTable = "CREATE TABLE DBMETA( "
					+ " metaid INT NOT NULL AUTO_INCREMENT "
					+ ", dbVersion INT " + ", PRIMARY KEY (metaid)); ";

			try {
				stmt = con.prepareStatement(createUserTable);
				stmt.execute();
				stmt.close();
				stmt = con.prepareStatement(createDBMetaTable);
				stmt.execute();

				// Setting the new DatabaseVersion
				logger.info("Database updated from Version " + dbVersion
						+ " to Version 1");
				CreateTable.setDBVersion(1);
				return 1;

			} catch (SQLException e) {
				logger.warn(e);
				logger.warn("Failed to update DBVersion");
				return dbVersion;
			} finally {
				try {
					if (stmt != null)
						stmt.close();
					if (con != null)
						con.close();
				} catch (SQLException e) {
					logger.warn("Couldn't close SQLInteraction. Exception: "
							+ e);
				}
			}

		}

		if (dbVersion == 1) {
			String createCategoryTable = null;
			String createEntriesTable = null;
			String createDailyTable = null;
			createCategoryTable = "CREATE TABLE CATEGORY( "
					+ " cid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT "
					+ " , name VARCHAR(32) "
					+ " , weight FLOAT "
					+ " , maximum FLOAT"
					+ " , minimum FLOAT"
					+ " , step FLOAT NOT NULL "
					+ " , unity VARCHAR(32) "
					+ " , isActive BOOLEAN "
					+ " , PRIMARY KEY (cid)"
					+ " , category_uid BIGINT UNSIGNED NOT NULL REFERENCES USER(uid) ON DELETE CASCADE); ";

			createEntriesTable = "CREATE TABLE ENTRIES( "
					+ " eid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT "
					+ " , date DATE "
					+ " , weight FLOAT "
					+ " , maximum FLOAT "
					+ " , minimum FLOAT "
					+ " , step FLOAT NOT NULL "
					+ " , unity VARCHAR(32) "
					+ " , value FLOAT "
					+ " , PRIMARY KEY (eid) "
					+ " , entries_cid BIGINT UNSIGNED NOT NULL REFERENCES CATEGORY(cid) ON DELETE CASCADE); ";

			createDailyTable = "CREATE TABLE DAILY("
					+ " did BIGINT UNSIGNED NOT NULL AUTO_INCREMENT "
					+ " , date DATE "
					+ " , bedtime DATETIME "
					+ " , waketime DATETIME "
					+ " , satisfaction INT "
					+ " , motivation INT "
					+ " , PRIMARY KEY (did) "
					+ " , daily_uid BIGINT UNSIGNED NOT NULL REFERENCES USER(uid) ON DELETE CASCADE); ";

			try {
				stmt = con.prepareStatement(createCategoryTable);
				stmt.execute();
				stmt.close();
				stmt = con.prepareStatement(createEntriesTable);
				stmt.execute();
				stmt.close();
				stmt = con.prepareStatement(createDailyTable);
				stmt.execute();

				// Setting the new DatabaseVersion
				logger.info("Database updated from Version " + dbVersion
						+ " to Version 2");
				CreateTable.setDBVersion(2);
				return 2;

			} catch (SQLException e) {
				logger.warn(e);
				logger.warn("Failed to update DBVersion");
				throw new Exception("DB Update Fail");
			} finally {
				try {
					if (stmt != null)
						stmt.close();
					if (con != null)
						con.close();
				} catch (SQLException e) {
					logger.warn("Couldn't close SQLInteraction. Exception: "
							+ e);
				}
			}
		}

		if (dbVersion == Settings.dbCurrentVersion) {
			logger.info("Database is up to date");
			return dbVersion;
		} else {
			logger.info("Error with updating Database");
			return dbVersion;
		}

	}
}